#! /bin/bash
export LANG=zh_CN.UTF-8
HIVE_HOME=/usr/bin/hive	

${HIVE_HOME} -S -e "


-- 第一个表:创建fact_customer_relationship(意向表)
--上传文件(deleted：合法性判断的字段，如果为1，表示这一条意向信息已经被删除，不用参加统计)
INSERT INTO hive.online_edu_dwd.fact_customer_relationship
SELECT id,
       create_date_time,
       update_date_time,
       deleted,
       customer_id,
       first_id,
       belonger,
       belonger_name,
       initial_belonger,
       distribution_handler,
       business_scrm_department_id,
       last_visit_time,
       next_visit_time,
       origin_type,
       itcast_school_id,
       itcast_subject_id,
       intention_study_type,
       anticipat_signup_date,
       level,
       creator,
       current_creator,
       creator_name,
       origin_channel,
       comment,
       first_customer_clue_id,
       last_customer_clue_id,
       process_state,
       process_time,
       payment_state,
       payment_time,
       signup_state,
       signup_time,
       notice_state,
       notice_time,
       lock_state,
       lock_time,
       itcast_clazz_id,
       itcast_clazz_time,
       payment_url,
       payment_url_time,
       ems_student_id,
       delete_reason,
       deleter,
       deleter_name,
       delete_time,
       course_id,
       course_name,
       delete_comment,
       close_state,
       close_time,
       appeal_id,
       tenant,
       total_fee,
       belonged,
       belonged_time,
       belonger_time,
       transfer,
       transfer_time,
       follow_type,
       transfer_bxg_oa_account,
       transfer_bxg_belonger_name,
       '9999-99-99' AS end_date,
       dt AS start_date
FROM hive.online_edu_ods.customer_relationship
WHERE deleted <> 1;


---------------------------------------------------
-- 第二个表:创建dim_itcast_clazz(报名课程表)
--上传文件
INSERT INTO hive.online_edu_dwd.dim_itcast_clazz
SELECT id,
       create_date_time,
       update_date_time,
       deleted,
       itcast_school_id,
       itcast_school_name,
       itcast_subject_id,
       itcast_subject_name,
       itcast_brand,
       clazz_type_state,
       clazz_type_name,
       teaching_mode,
       start_time,
       end_time,
       comment,
       detail,
       uncertain,
       tenant,
       '9999-99-99' AS end_date,
       dt AS start_date
FROM hive.online_edu_ods.itcast_clazz;

-------------------------------------------------------
-- 第三个表:创建dim_employee(员工表)
--上传文件
INSERT INTO hive.online_edu_dwd.dim_employee
SELECT id,
       email,
       real_name,
       phone,
       department_id,
       department_name,
       remote_login,
       job_number,
       cross_school,
       last_login_date,
       creator,
       create_date_time,
       update_date_time,
       deleted,
       scrm_department_id,
       leave_office,
       leave_office_time,
       reinstated_time,
       superior_leaders_id,
       tdepart_id,
       tenant,
       ems_user_name,
       '9999-99-99' AS end_date,
       dt AS start_date
FROM hive.online_edu_ods.employee;

-------------------------------------------------------
-- 第四个表:创建dim_scrm_department(部门表)
--上传文件
INSERT INTO hive.online_edu_dwd.dim_scrm_department
SELECT
    id,
    name,
    parent_id,
    create_date_time,
    update_date_time,
    deleted,
    id_path,
    tdepart_code,
    creator,
    depart_level,
    depart_sign,
    depart_line,
    depart_sort,
    disable_flag,
    tenant,
    '9999-99-99' AS end_date,
    dt AS start_date
FROM hive.online_edu_ods.scrm_department;

-------------------------------------------------------
-- 第五个表:创建dim_scrm_department(部门表)
--上传文件
INSERT INTO hive.online_edu_dwd.dim_scrm_department
SELECT id,
       name,
       parent_id,
       create_date_time,
       update_date_time,
       deleted,
       id_path,
       tdepart_code,
       creator,
       depart_level,
       depart_sign,
       depart_line,
       depart_sort,
       disable_flag,
       tenant,
       '9999-99-99' AS end_date,
       dt AS start_date
FROM hive.online_edu_ods.scrm_department;

-------------------------------------------------------
-- 第六个表:创建dim_customer_appeal(线索申诉信息表)
--上传文件
INSERT INTO hive.online_edu_dwd.dim_customer_appeal
SELECT
    id,
    customer_relationship_first_id,
    employee_id,
    employee_name,
    employee_department_id,
    employee_tdepart_id,
    appeal_status,
    audit_id,
    audit_name,
    audit_department_id,
    audit_department_name,
    audit_date_time,
    create_date_time,
    update_date_time,
    deleted,
    tenant,
    '9999-99-99' AS end_date,
    dt AS start_date
FROM hive.online_edu_ods.customer_appeal;

-------------------------------------------------------
-- 第七个表:创建dim_itcast_school(学校表)
--上传文件
INSERT INTO hive.online_edu_dwd.dim_itcast_school
SELECT
    id,
    create_date_time,
    update_date_time,
    deleted,
    name,
    code,
    tenant,
    '9999-99-99' AS end_date,
    dt AS start_date
FROM hive.online_edu_ods.itcast_school;

-------------------------------------------------------
-- 第八个表:创建dim_itcast_subject(学科表)
--上传文件
INSERT INTO hive.online_edu_dwd.dim_itcast_subject
SELECT
    id,
    create_date_time,
    update_date_time,
    deleted,
    name,
    code,
    tenant,
    '9999-99-99' AS end_date,
    dt AS start_date
FROM hive.online_edu_ods.itcast_subject;
